package de.hmm.hwip.configuration.sysdevice;

import de.hmm.hwip.configuration.api.ISystemDeviceConf;

/**
 * Data base query for System_Dev service
 * 
 * generates query for requested database operation  
 * 
 * @author johannes mendel
 * @version 16.10.2012
 *
 */
public class SystemDeviceImpl implements ISystemDeviceConf {

		/**
	 * function to get the query for getting system device by ID
	 * 
	 * @param id: primary key of system device
	 * @return query to get system device
	 */
	@Override
	public String getQueryForSysDeviceByID(int sysDevId){
		String query = ""; 
		
		query = "SELECT * FROM sys_device WHERE sys_dev_id = " + sysDevId + ";";
		
		return query;
	}
	
	/**
	 * function to get the query for getting system device by serial number and device_id_fk
	 * 
	 * @param radio radio id foreign key from device
	 * @param sn: serial number of system device
	 * @return query to get system device
	 */
	@Override
	public String getQueryForSysDeviceBySN(int radio, String sn){
		String query = ""; 
		
		query = "SELECT s.* FROM sys_device AS s " +
				"JOIN device AS d ON s.device_id_fk = d.device_id " +
				"WHERE d.rt_id_fk = " + radio + " AND s.sn_addr = '" + sn + "';";
		
		return query;
	}
	
	/**
	 * function to get the query for getting system device by device_id_fk and spot_id_fk
	 * 
	 * @param deviceID: foreign key of device_id_fk
	 * @param spotID: foreign key of spot_id_fk
	 * @return query to get system device
	 */
	@Override
	public String getQueryForSysDevice(int deviceID, int spotID){
		String query = ""; 
		
		query = "SELECT * FROM sys_device WHERE spot_id_fk = " + spotID + " AND device_id_fk = " + deviceID + ";";
		
		return query;
	}
	
	/**
	 * function to get the query for setting system device
	 *  
	 * @param deviceID: foreign key of device_id_fk
	 * @param spotID: foreign key of spot_id_fk
	 * @param sn_addr: serial number of device
	 * @param specification: specification of device -> optional
	 * @param attribute: String[] attributes of device
	 * @return query to set system device
	 */
	@Override
	public String getQueryForSetSysDevice(int deviceID, int spotID, String sn_addr, String specification, String[] attribute){
		String query = ""; 
		
		query = "INSERT INTO sys_device (" +
					"spot_id_fk, device_id_fk, sn_addr, sys_specific";
					for(int i = 0; i < attribute.length; i++){
						query += " , attr_value_"+ (i + 1) ;
					}
				query += " ) VALUES (" +
					deviceID + " , " +
					spotID + " , '" +
					sn_addr + "', '" +
					specification;
					for(int i = 0; i < attribute.length; i++){
						query += "', '" + attribute[i] ;
					}
					query += "');";
		
		return query;
	}
	
	/**
	 * function to get the query for setting system device
	 *  
	 * @param sysDevId: sys_dev_id primary key 
	 * @param deviceID: foreign key of device_id_fk
	 * @param spotID: foreign key of spot_id_fk
	 * @param sn_addr: serial number of device
	 * @param specification: specification of device -> optional
	 * @param attribute: String[] attributes of device
	 * @return query to set system device
	 */
	@Override
	public String getQueryForSetSysDevice(int sysDevId, int deviceIDFk, int spotID, String sn_addr, String specification, String[] attribute){
		String query = ""; 
		
		query = "INSERT INTO sys_device (" +
					"sys_dev_id, spot_id_fk, device_id_fk, sn_addr, sys_specific ";
		for(int i = 0; i < attribute.length; i++){
			query += " , attr_value_"+ (i + 1) ;
		}  
		
		query += " ) VALUES (" +
				sysDevId + " , " +
				spotID + " , " +
				deviceIDFk + " , '" +
				sn_addr + "', '" +
				specification;
		
		for(int i = 0; i < attribute.length; i++){
			query += "', '" + attribute[i] ;
		}
		query += "');";
		
		return query;
	}
	
	/**
	 * function to get the query for setting system device
	 *  
	 * @param sysDevId: primary key of sysDevId
	 * @param attribute: String[] attributes of device
	 * @return query to set system device
	 */
	@Override
	public String getQueryForUpdateSysDevice(int sysDevId, String[] attribute){
		String query = ""; 
		
		query = "UPDATE sys_device SET ";
				for(int i = 0; i < attribute.length ; i++){
					if(i < 1){
						query += "attr_value_" + (i + 1) + " = '" + attribute[i] + "' ";
					}else{
						query += ", attr_value_" + (i + 1) + " = '" + attribute[i] + "' ";
					}
				}
				
				query += "WHERE sys_dev_id = " + sysDevId + " ;";
		
		return query;
	}
	
	/**
	 * function to get the query for updating system device
	 *  
	 * @param sysDevId: primary key of sys_dev_id
	 * @param deviceID: foreign key of device_id_fk
	 * @param spotID: foreign key of spot_id_fk
	 * @param sn_addr: serial number of device
	 * @param specification: specification of device -> optional
	 * @param attribute: String[] attributes of device
	 * @return query to update system device
	 */
	@Override
	public String getQueryForUpdateSysDevice(int sysDevId, int deviceID, int spotID, String sn_addr, String specification, String[] attribute){
		String query = ""; 
		
		query = "UPDATE sys_device SET " + 
					"spot_id_fk = " + spotID + ", " + 
					"device_id_fk = " + deviceID + ", " + 
					"sn_addr = '" + sn_addr + "', " +  
					"sys_specific = '" + specification; ///	+ "', " + 
					for(int i = 0 ; i < attribute.length ; i++){
						query += "' , attr_value_" + (i + 1) + " = '" + attribute[i] ;
					}

					query += "' WHERE (sys_dev_id = " + sysDevId +");";
		
		return query;
	}
	
	/**
	 * function to get the query for deleting system device
	 *  
	 * @param deviceID: foreign key of device_id_fk
	 * @return query to delete system device
	 */
	@Override
	public String getQeuryForDeleteSysDevice(int sysDevId){
		String query = ""; 
		
		query = "DELETE FROM sys_device WHERE sys_dev_id = " + sysDevId + ";";
		
		return query;
	}

	/**
	 * function to get the query for getting device 
	 *  
	 * @param radiotype: foreign key of rt_id_fk
	 * @param modeltype: model_type of device
	 * @return query to get device
	 */
	@Override
	public String getQueryForDeviceId(int radiotype, String modeltype) {
		String query = "";
		
		query = "SELECT device_id FROM device WHERE rt_id_fk = " + radiotype + " AND model_type = '" + modeltype + "' ;";
		
		return query;
	}

	@Override
	/**
	 * function to get the query for updating system device
	 *  
	 * @param sysDevId: primary key of sysDevId
	 * @param spotId: spot_id_fk
	 * @return query to update spot_id_fk of system device
	 */
	public String getQueryForUpdateSpotSysDevice(int sysDevId, int spotId){
		String query = ""; 
		
		query = "UPDATE sys_device SET " +
				"spot_id_fk = " + spotId +
				" WHERE sys_dev_id = " + sysDevId + " ;";
		
		return query;
	}
	
	/**
	 * function to get the query to look for entries in connected_devices
	 *  
	 * @param sysDevId: foreign key of sysDevId
	 * @return query to find scripts which are conncted to device
	 */
	@Override
	public String getQueryForConDevLookUp(int sysDevId){
		String query = "";
		
		query = "SELECT s.ip_address, c.value_1, com.attr, c.command_id_fk,sys.sys_dev_id, sys.device_id_fk, sys.sn_addr, c.cs_id, c.command_id_fk " +
				"FROM connected_devices AS con " +
				"JOIN controlscript AS c ON con.cs_id_fk = c.cs_id " +
				"JOIN command AS com ON c.command_id_fk = com.command_id " +
				"JOIN sys_device AS sys ON sys.sys_dev_id = c.sys_dev_id_fk " +
				"JOIN spot AS s ON s.spot_id = sys.spot_id_fk " +
				"WHERE con.sys_dev_id_fk = " + sysDevId + " ;";
		
		return query;
	}
	
	/**
	 * function to get the query for getting all data from device by device_id 
	 *  
	 * @param deviceId: primary key of device_id
	 * @return query to get device
	 */
	@Override
	public String getQueryForDeviceByDeviceId(int deviceId) {
		String query = "";
		
		query = "SELECT * FROM device WHERE device_id = " + deviceId + " ;";
		
		return query;
	}
	
	/**
	 * function to get the query for getting all spot_ips
	 * 
	 * @return query to get all spot ips
	 */
	@Override
	public String getQueryForSpotIps(){
		String query = "";
		
		query = "SELECT ip_address FROM spot;";
		
		return query;
	}
	/**
	 * 	CREATE TABLE sys_device 
			(sys_dev_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
			spot_id_fk INT NOT NULL,
			device_id_fk INT NOT NULL,
			sn_addr TEXT,
			sys_specific TEXT,
			attr_value_1 TEXT,
			attr_value_2 TEXT,
			attr_value_3 TEXT,
			attr_value_4 TEXT,
			attr_value_5 TEXT,
			FOREIGN KEY (spot_id_fk) REFERENCES spot(spot_id) ON DELETE RESTRICT,
			FOREIGN KEY (device_id_fk) REFERENCES device(device_id) ON DELETE RESTRICT
		);
		
	 */
}
